<?php
namespace Index\Controller;
use Think\Controller;
class IndexController extends Controller {

    public function indexAction(){
    	$Model = M();
    	$id = I('session.uid');

    	//全部课程分类
    	$sql = "select * from category";
    	$category = $Model->query($sql);
    	$categoryInfo= $this->_fibTree($category,0);
		$this->assign('categoryInfo',$categoryInfo);
        //用户信息
		$sql = "select username from user where id ='{$id}'";
		$userInfo = $Model->query($sql);
		$userInfo = $userInfo[0];
		$this->assign('userInfo',$userInfo);

        //6个广告轮展图
        $sql = "select title,url,backgroundcolor,courseid from banner";
        $bannerInfo = $Model->query($sql);
        $this->assign('bannerInfo',$bannerInfo);

        //今日直播
        $sql = "select t1.starttime,t1.endtime,t2.id,t2.coursename,t2.picture,t3.teachername from courselist t1 left join course t2 on t1.courseid = t2.id left join teacher t3 on t1.courseid = t3.courseid where to_days(date_format(from_UNIXTIME(t1.endtime),'%Y-%m-%d')) = to_days(now()) order by t1.starttime asc";
        $liveToday = $Model->query($sql);
        $this->assign('liveToday',$liveToday);

        //大学-英语四级
        $cet4Info = $this->_classification2nd("英语四级");
        $this->assign('cet4Info',$cet4Info);

        //大学-英语六级
        $cet6Info = $this->_classification2nd("英语六级");
        $this->assign('cet6Info',$cet6Info);
       
        //大学-专四专八
        $tem4andtem8 = $this->_classification2nd("专四专八");
        $this->assign('tem4andtem8',$tem4andtem8);

        //大学-免费排行
        $universityFree = $this->_freeInfo("大学");
        $this->assign('universityFree',$universityFree);
       
        //大学-付费排行
        $universityPay = $this->_payInfo("大学");
        $this->assign('universityPay',$universityPay);

        //研究生-考研英语
        $postgraduateEnglish = $this->_classification2nd("考研英语");
        $this->assign('postgraduateEnglish',$postgraduateEnglish);

        //研究生-考研政治
        $postgraduatePolitics = $this->_classification2nd("考研政治");
        $this->assign('postgraduatePolitics',$postgraduatePolitics);

        //研究生-考研复试
        $postgraduateRetest = $this->_classification2nd("考研复试");
        $this->assign('postgraduateRetest',$postgraduateRetest);

        //研究生-免费排行
        $postgraduateFree = $this->_freeInfo("研究生");
        $this->assign('postgraduateFree',$postgraduateFree);
        
        //研究生-付费排行
        $postgraduatePay = $this->_payInfo("研究生");
        $this->assign('postgraduatePay',$postgraduatePay);

        //中小学-高考
        $collegeEntrance = $this->_classification2nd("高考");
        $this->assign('collegeEntrance',$collegeEntrance);

        //中小学-中考
        $schoolEntrance = $this->_classification2nd("中考");
        $this->assign('schoolEntrance',$schoolEntrance);

        //中小学-小学
        $primarySchool = $this->_classification2nd("小学");
        $this->assign('primarySchool',$primarySchool);

        //中小学-免费排行
        $schoolFree = $this->_freeInfo("中小学");
        $this->assign('schoolFree',$schoolFree);
        
        //中小学-付费排行
        $schoolPay = $this->_payInfo("中小学");
        $this->assign('schoolPay',$schoolPay);

        //英语学习-英美文学
        $literature= $this->_classification2nd("英美文学");
        $this->assign('literature',$literature);

        //英语学习-英语口语
        $oralEnglish = $this->_classification2nd("英语口语");
        $this->assign('oralEnglish',$oralEnglish);

        //英语学习-新概念
        $newConcept = $this->_classification2nd("新概念");
        $this->assign('newConcept',$newConcept);

        //英语学习-免费排行
        $EnglishFree = $this->_freeInfo("英语学习");
        $this->assign('EnglishFree',$EnglishFree);
        
        //英语学习-付费排行
        $EnglishPay = $this->_payInfo("英语学习");
        $this->assign('EnglishPay',$EnglishPay);

        //出国留学-托福
        $TOEFL= $this->_classification2nd("托福");
        $this->assign('TOEFL',$TOEFL);

        //出国留学-雅思
        $IELTS = $this->_classification2nd("雅思");
        $this->assign('IELTS',$IELTS);

        //出国留学-免费排行
        $abroadFree = $this->_freeInfo("出国留学");
        $this->assign('abroadFree',$abroadFree);
        
        //出国留学-付费排行
        $abroadPay = $this->_payInfo("出国留学");
        $this->assign('abroadPay',$abroadPay);

        //小语种-日语
        $Japanese= $this->_classification2nd("日语");
        $this->assign('Japanese',$Japanese);

        //小语种-韩语
        $Korean = $this->_classification2nd("韩语");
        $this->assign('Korean',$Korean);

        //小语种-免费排行
        $smallLanguagesFree = $this->_freeInfo("小语种");
        $this->assign('smallLanguagesFree',$smallLanguagesFree);
        
        //小语种-付费排行
        $smallLanguagesPay = $this->_payInfo("小语种");
        $this->assign('smallLanguagesPay',$smallLanguagesPay);

        //老师排行榜
        $sql = "select count(*) as num,t3.teachername,t3.teacherface,t3.id from (select t1.courseid,t2.teachername,t2.teacherface,t2.id from application t1 left join teacher t2 on t1.courseid = t2.courseid) as t3 group by t3.teachername order by num desc limit 8";
        $teacherRanking = $Model->query($sql);
        $this->assign('teacherRanking',$teacherRanking);

        $sql = "select *,sum(t3.apply),ifnull(sum(t3.apply),0) as applynum,count(t3.courseid) as coursenum from (select t1.courseid,t1.id,t1.teachername,t1.teacherface,t1.brief,t2.apply from teacher as t1 left join (select courseid,count(*) as apply from application group by courseid) as t2 on t1.courseid =t2.courseid) as t3 group by t3.teachername order by t3.apply desc limit 12";
        $teacher = $Model->query($sql);
        $teacherInfo = array();
        $teacherInfo[0][0] = array_slice($teacher,0,2);
        $teacherInfo[1][0] = array_slice($teacher,2,2);
        $teacherInfo[2][0]= array_slice($teacher,4,2);
        $teacherInfo[3][0] = array_slice($teacher,6,2);
        $teacherInfo[4][0] = array_slice($teacher,8,2);
        $teacherInfo[5][0] = array_slice($teacher,10,2);
        $this->assign('teacherInfo',$teacherInfo);

        $this->display('index');
    }

    //全部课程分类调用方法
	private function _fibTree($arr, $fid = 0) {
        $tree = array();
        $temp = array();
        if($arr) {
            foreach($arr as $k=>$v) {
                if($v['fid'] == $fid) {
                    $temp = $this->_fibTree($arr, $v['id']);
                    $temp AND $v['son'] = $temp;
                    $tree[] = $v;
                }
            }
        }
        return $tree;
    }     

	//循环获取某个分类下的子分类
    private function _getSonId($fid){
    	$Model = M();
    	$sql = "select * from category where fid = '{$fid}' ";
    	$sonArr = $Model->query($sql);
    	foreach ($sonArr as $key => $value) {
    		$str .= $value['id'];
    		$str .= ",";
    		$str .= $this->_getSonId($value['id']);
    	}
    	return $str;
    }   

    //获取二级分类相关信息
    private function _classification2nd($keyword){
        $time = time();
        $Model = M();
        $sql = "select id from category where categoryname = '{$keyword}'";
        $categoryid = $Model->query($sql);
        $sql = "select t1.id,t1.coursename,t1.picture,t1.introduction,t1.price,t2.starttime,t2.endtime,t3.teachername,t4.applynum from course t1 left join courselist t2 on t1.id = t2.courseid left join teacher t3 on t1.id = t3.courseid left join (select courseid,count(*) applynum from application group by courseid) t4 on t1.id = t4.courseid where t1.categoryid = '{$categoryid[0]['id']}' and t2.starttime > '{$time}' group by t2.courseid order by t2.starttime asc limit 6";
        $Info = $Model->query($sql);
        return $Info;
    }   

    //获取免费的相关信息
    private function _freeInfo($keyword){
        $time = time();
        $Model = M();
        $sql = "select id from category where categoryname = '{$keyword}'";
        $categoryid = $Model->query($sql);
        $sonId = $this->_getSonId($categoryid[0]['id']);
        $sonId.= 0;
        $sql = "select t5.*,(@y:=@y+1) as num from (select t1.id,t1.coursename,t2.teachername,t3.applynum,t4.endtime from course as t1 left join teacher as t2 on t1.id = t2.courseid left join (select courseid,count(*) as applynum from application group by courseid) as t3 on t1.id = t3.courseid left join (select courseid,max(endtime) as endtime from courselist group by courseid) as t4 on t1.id = t4.courseid  where t1.price = 0 and t1.categoryid in ({$sonId}) and t4.endtime > '{$time}') as t5 join (select @y:=0) as t6 order by t5.applynum desc limit 5";
        $Info = $Model->query($sql);
        return $Info;
    }

    //获取付费的相关信息
    private function _payInfo($keyword){
        $time = time();
        $Model = M();
        $sql = "select id from category where categoryname = '{$keyword}'";
        $categoryid = $Model->query($sql);
        $sonId = $this->_getSonId($categoryid[0]['id']);
        $sonId.= 0;
        $sql = "select t5.*,(@y:=@y+1) as num from (select t1.id,t1.coursename,t2.teachername,t3.applynum,t4.endtime from course as t1 left join teacher as t2 on t1.id = t2.courseid left join (select courseid,count(*) as applynum from application group by courseid) as t3 on t1.id = t3.courseid left join (select courseid,max(endtime) as endtime from courselist group by courseid) as t4 on t1.id = t4.courseid  where t1.price != 0 and t1.categoryid in ({$sonId}) and t4.endtime > '{$time}') as t5 join (select @y:=0) as t6 order by t5.applynum desc limit 5";
        $Info = $Model->query($sql);
        return $Info;
    }

}